﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace text1
{

    public partial class zhxx : Form
    {
        string constr = "server=LAPTOP-NHBSREVG;database=社区数据库;uid=sa;pwd=990823";


        public zhxx()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //获得文本框的值
            String text1 = textBox1.Text.Trim();
            String text2 = textBox2.Text.Trim();
            String text3 = textBox3.Text.Trim();
            String text4 = comboBox1.Text.Trim();
            String text5 = textBox5.Text.Trim();
            String text6 = textBox6.Text.Trim();
            String text7 = comboBox2.Text.Trim();
            String text8 = textBox8.Text.Trim();


            //参数
            SqlParameter para_text1;
            SqlParameter para_text2;
            SqlParameter para_text3;
            SqlParameter para_text4;
            SqlParameter para_text5;
            SqlParameter para_text6;
            SqlParameter para_text7;
            SqlParameter para_text8;


            //创建连接并打开
            SqlConnection cnn = new SqlConnection(constr);
       
            cnn.Open();

            //创建查询语句
            String find = "select 房间.房间号,住户.住户身份证号,姓名,性别,生日,电话号码,是否户主,入住时间 " +
                "from 房间,住户,住户居住 "
                + "where 房间.房间号=住户居住.房间编号 and 住户居住.住户身份证号=住户.住户身份证号 ";

            //根据textbox的值是否为空，进行进一步的判断，将sql语句补充完整
            if (text1.Length != 0)
            {
          
                find += "and 房间.房间号=@text1 ";
            }
            if (text2.Length != 0)
            {

                find += "and 住户姓名=@text2 ";
            }
            if (text3.Length != 0)
            {

                find += "and 住户.住户身份证号=@text3 ";
            }
            if (text4.Length != 0)
            {

                find += "and 性别=@text4 ";
            }
            if (text5.Length != 0)
            {

                find += "and 生日=@text5 ";
            }
            if (text6.Length != 0)
            {

                find += "and 电话号码=@text6 ";
            }
            if (text7.Length != 0)
            {

                find += "and 是否户主=@text7 ";
            }
            if (text8.Length != 0)
            {

                find += "and 入住时间=@text8 ";
            }


            //创建sqlDataAdapter
            SqlDataAdapter myDa = new SqlDataAdapter(find, cnn);
            if (text1.Length != 0)
            {
                para_text1 = new SqlParameter("@text1", text1);
                myDa.SelectCommand.Parameters.Add(para_text1);
            }

            if (text2.Length != 0)
            {
                para_text2 = new SqlParameter("@text2", text2);
                myDa.SelectCommand.Parameters.Add(para_text2);
            }

            if (text3.Length != 0)
            {
                para_text3 = new SqlParameter("@text3", text3);
                myDa.SelectCommand.Parameters.Add(para_text3);
            }

            if (text4.Length != 0)
            {
                para_text4 = new SqlParameter("@text4", text4);
                myDa.SelectCommand.Parameters.Add(para_text4);
            }

            if (text5.Length != 0)
            {
                para_text5 = new SqlParameter("@text5", text5);
                myDa.SelectCommand.Parameters.Add(para_text5);
            }

            if (text6.Length != 0)
            {
                para_text6 = new SqlParameter("@text6", text6);
                myDa.SelectCommand.Parameters.Add(para_text6);
            }

            if (text7.Length != 0)
            {
                para_text7 = new SqlParameter("@text7", text7);
                myDa.SelectCommand.Parameters.Add(para_text7);
            }

            if (text8.Length != 0)
            {
                para_text8 = new SqlParameter("@text8", text8);
                myDa.SelectCommand.Parameters.Add(para_text8);
            }

            //创建DataSet用来储存数据
            DataSet DS = new DataSet();
            //将数据填充到DataSet中
            myDa.Fill(DS, "performtable");
            dataGridView1.DataSource = DS.Tables["performtable"];
            //关闭连接
            cnn.Close();
        }

        private void label3_Click(object sender, EventArgs e)
        {

        }

        private void delete_Click(object sender, EventArgs e)
        {
            //获得文本框的值

            String text2 = textBox2.Text.Trim();
            String text3 = textBox3.Text.Trim();
           



            //创建连接并打开
            SqlConnection cnn = new SqlConnection(constr);

            cnn.Open();


            string sqlstr=" ";

           
            if(text2.Length == 0 && text3.Length == 0)
            {
                MessageBox.Show("删除失败！请至少输入要删除住户的姓名或者身份证号！");
                return;
    
            }

            else if (text2.Length !=0 && text3.Length !=0)
            {

                sqlstr = "delete from 住户 where 姓名= '" + text2 + "' and 住户身份证号= '" + text3 + "'   delete from " +
                    " 住户居住 where 住户身份证号 in (select 住户身份证号 from 住户 where 姓名=  '"+text2+"' and " +
                    "住户身份证号= '"+text3+"')    update 房间 set 住户人数-=1 where 房间号 in (select 房间编号 from 住户居住 " +
                    "where 住户身份证号 in  (select 住户身份证号 from 住户 where 姓名= '"+text2+"' and " +
                    "住户身份证号 = '"+text3+"' ))";
            }

            else if (text2.Length != 0)
            {
                
                sqlstr = "delete from 住户 where 姓名= '" + text2 + "'  delete from  " +
                    " 住户居住 where 住户身份证号 in (select 住户身份证号 from 住户 where 姓名=  '" + text2 + 
                     "')   update 房间 set 住户人数-=1 where 房间号 in (select 房间编号 from 住户居住 " +
                    "where 住户身份证号 in  (select 住户身份证号 from 住户 where 姓名= '" + text2 + "' ))"; 
            }
            else if (text3.Length != 0)
            {

                sqlstr = "delete from 住户 where 住户身份证号= '" + text3 + "'  delete from  " +
                    " 住户居住 where 住户身份证号 in (select 住户身份证号 from 住户 where 住户身份证号=  '" + text3 +
                     "')    update 房间 set 住户人数-=1 where 房间号 in (select 房间编号 from 住户居住 " +
                    "where 住户身份证号 in  (select 住户身份证号 from 住户 where 住户身份证号= '" + text3 + "' ))";
            }
           

            SqlCommand sqlcmd = new SqlCommand(sqlstr,cnn);

         
            sqlcmd.ExecuteNonQuery();
            MessageBox.Show("删除住户信息成功！ ");
            string find= "select * from 住户 ";
            SqlDataAdapter myDa = new SqlDataAdapter(find,cnn);
            DataSet DS = new DataSet();
            //将数据填充到DataSet中
            myDa.Fill(DS, "performtable");
            // 在dataGridView中显示
            dataGridView1.DataSource = DS.Tables["performtable"];
            //关闭连接
            cnn.Close();
          
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {
            
        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void textBox7_TextChanged(object sender, EventArgs e)
        {

        }

        private void label7_Click(object sender, EventArgs e)
        {

        }

        private void textBox5_TextChanged(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            //获得文本框的值
            String text1 = textBox1.Text.Trim();
            String text2 = textBox2.Text.Trim();
            String text3 = textBox3.Text.Trim();
            String text4 = comboBox1.Text.Trim();
            String text5 = textBox5.Text.Trim();
            String text6 = textBox6.Text.Trim();
            String text7 = comboBox2.Text.Trim();
            String text8 = textBox8.Text.Trim();

            if(text1.Length==0 || text2.Length==0 || text3.Length == 0 || text4.Length == 0 || text5.Length == 0 || text6.Length == 0
                || text7.Length == 0 || text8.Length == 0)
            {
                MessageBox.Show("增加失败！在增加用户前请填写每一项用户信息！");
                return;
            }
            //创建连接并打开
            SqlConnection cnn = new SqlConnection(constr);
            cnn.Open();
            string sqlstr = "insert into 住户 values ('"+text3+"','"+text2+ "','"+text4 + "','" +text5 + "','"
                + text6 + "','"+text7+ "')  insert into 住户居住 values ('"+text1 + "','" +text3+"','"+text8+"') "
                +"update 房间 set 住户人数+=1 where 房间号= '"+text1+"'";
            SqlCommand sqlcmd = new SqlCommand(sqlstr, cnn);

            
            sqlcmd.ExecuteNonQuery();
            MessageBox.Show("增加用户信息成功！");
            string find = "select * from 住户 ";
            SqlDataAdapter myDa = new SqlDataAdapter(find, cnn);
            DataSet DS = new DataSet();
            //将数据填充到DataSet中
            myDa.Fill(DS, "performtable");
            // 在dataGridView中显示
            dataGridView1.DataSource = DS.Tables["performtable"];
            //关闭连接
            cnn.Close();

        }

        private void button3_Click(object sender, EventArgs e)
        {
            String text1 = textBox1.Text.Trim();
            String text2 = textBox2.Text.Trim();
            String text3 = textBox3.Text.Trim();
            String text4 = comboBox1.Text.Trim();
            String text5 = textBox5.Text.Trim();
            String text6 = textBox6.Text.Trim();
            String text7 = comboBox2.Text.Trim();
            String text8 = textBox8.Text.Trim();

            if (text2.Length == 0 || text3.Length == 0 )
            {
                MessageBox.Show("修改失败！在修改住户前请填写住户的身份证号和姓名！");
                return;
            }

            SqlConnection cnn = new SqlConnection(constr);
            cnn.Open();
            
            string sqlstr = "update 住户 set ";
            if (text4.Length != 0)
            {

                sqlstr += " 性别= '"+text4+"' ";
            }
            if (text5.Length != 0)
            {

                sqlstr += " ,生日= '" + text5 + "' ";
            }
            if (text6.Length != 0)
            {

                sqlstr += " ,电话号码= '" + text6 + "' ";
            }
            if (text7.Length != 0)
            {

                sqlstr += " ,是否户主= '" + text7 + "' ";
            }
            sqlstr += "where 住户身份证号= '"+text3+"' and 姓名= '"+text2+"'    ";
            if (text8.Length!=0)
                sqlstr += "  update 住户居住 set 入住时间 ='"+text8+"' where 住户身份证号 = '"+text3+"' ";
            SqlCommand sqlcmd = new SqlCommand(sqlstr, cnn);

            sqlcmd.ExecuteNonQuery();
            string find = "select * from 住户 ";
            SqlDataAdapter myDa = new SqlDataAdapter(find, cnn);
            DataSet DS = new DataSet();
            //将数据填充到DataSet中
            myDa.Fill(DS, "performtable");
            // 在dataGridView中显示
            dataGridView1.DataSource = DS.Tables["performtable"];
            //关闭连接
            cnn.Close();
        }

        private void zhxx_Load(object sender, EventArgs e)
        {
            comboBox1.Items.Add("男");
            comboBox1.Items.Add("女");
            comboBox2.Items.Add("是");
            comboBox2.Items.Add("否");

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}
